/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.foxline.service;

import br.com.foxline.enums.TypeDocument;
import br.com.foxline.model.*;
import java.util.List;
import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.Query;

/**
 *
 * @author roniere
 */
@Stateless
@LocalBean
public class CharterTestamentService extends GenericService<CharterTestament> {

    public CharterTestamentService() {
        super(CharterTestament.class);
    }

    public List<CharterTestament> searchCharter(CharterTestament charter, People people, Stamp stamp) {

        Query query;
        String sql = "Select distinct(c) from CharterTestament c ";

        if (charter.getActCep() != null) {
            sql += " JOIN c.ActCep ac  ";
        }
        if (people != null) {
        if ((people.getName() != null && !people.getName().isEmpty())
                        || (people.getTipoDocumento() == TypeDocument.CPF && people.getCpf() != null && !people.getCpf().isEmpty())
                        || (people.getTipoDocumento() == TypeDocument.CNPJ && people.getCnpj() != null && !people.getCnpj().isEmpty())) {
                    sql += " JOIN c.people peo ";
        }
        }
        
        if (stamp != null && stamp.getTypeStamp() != null) {
                sql += " JOIN c.stamp st JOIN st.typeStamp ts";
            
        }

        sql += " where ";


        if (charter.getActCep() != null) {
            sql += " ac.id = :idActCep and ";
        }
        if (charter.getEsc() != null && !charter.getEsc().isEmpty()) {
            sql += " ac.esc like :esc and ";
        }
        if (charter.getBook() != null && !charter.getBook().isEmpty()) {
            sql += " c.book like :book  and ";
        }
        if (charter.getDateTest() != null) {
            sql += " c.dateTest like :dateTest  and ";
        }
        if (charter.getBookComp() != null && !charter.getBookComp().isEmpty()) {
            sql += " c.bookComp like :bookComp  and ";
        }
        if (charter.getLeaf() != null && !charter.getLeaf().isEmpty()) {
            sql += " c.leaf like :leaf and ";
        }
        if (charter.getLeafComp() != null && !charter.getLeafComp().isEmpty()) {
            sql += " c.leafComp like :leafComp and ";
        }

        if (people != null) {
            if (people.getName() != null && !people.getName().isEmpty()) {
                sql += " lower(peo.name) like lower(:name) and ";
            }
            if (people.getTipoDocumento() == TypeDocument.CPF && people.getCpf() != null && !people.getCpf().isEmpty()) {
                sql += " lower(peo.cpf) like lower(:doc)  and ";
            } else if (people.getTipoDocumento() == TypeDocument.CNPJ && people.getCnpj() != null && !people.getCnpj().isEmpty()) {
                sql += " lower(peo.cnpj) like lower(:doc)  and ";
            }
        }
        if (stamp != null) {
            if (stamp.getSerie() != null && !stamp.getSerie().isEmpty()) {
                sql += " st.serie like :serie and ";
            }
            if (stamp.getNumber() != null) {
                sql += " st.number = :number and ";
            }
            if (stamp.getTypeStamp() != null) {
                sql += " ts.id = :idTypeStamp  and ";
            }
        }

        sql += " 1=1  ORDER BY c.esc ASC";

        query = getEntityManager().createQuery(sql);

        if (charter.getActCep() != null) {
            query.setParameter("idActCep", charter.getActCep().getId());
        }
        if (charter.getBook() != null && !charter.getBook().isEmpty()) {
            query.setParameter("book", charter.getBook());
        }
        if (charter.getDateTest() != null) {
            query.setParameter("dateTest", charter.getDateTest());
        }
        if (charter.getBookComp() != null && !charter.getBookComp().isEmpty()) {
            query.setParameter("bookComp", charter.getBookComp());
        }
        if (charter.getLeaf() != null && !charter.getLeaf().isEmpty()) {
            query.setParameter("leaf", charter.getLeaf());
        }
        if (charter.getLeafComp() != null && !charter.getLeafComp().isEmpty()) {
            query.setParameter("leafComp", charter.getLeaf());
        }
        if (people != null) {
            if (people.getName() != null && !people.getName().isEmpty()) {
                query.setParameter("name", people.getName() + "%");
            }
            if ((people.getCpf() != null && !people.getCpf().isEmpty())
                    || (people.getCnpj() != null && !people.getCnpj().isEmpty())) {
                query.setParameter("doc",
                        people.getTipoDocumento() == TypeDocument.CPF ? people.getCpf() : people.getCnpj());
            }

        }
        if (charter.getEsc() != null && !charter.getEsc().isEmpty()) {
            query.setParameter("esc", charter.getEsc());
        }
        if (stamp != null) {
            if (stamp.getSerie() != null && !stamp.getSerie().isEmpty()) {
                query.setParameter("serie", stamp.getSerie());
            }
            if (stamp.getNumber() != null) {
                query.setParameter("number", stamp.getNumber());
            }
            if (stamp.getTypeStamp() != null) {
                query.setParameter("idTypeStamp", stamp.getTypeStamp().getId());
            }
        }
        System.err.println(sql + " = " + query.getResultList().size());
        return query.getResultList();
    }
}
